﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using QuanLyBanHang_NewApp.DBAccess;


namespace QuanLyBanHang.DAL
{
    public class SanPhamDAL
    {
        dbConnection conn;
        public SanPhamDAL()
        {
            conn = new dbConnection();
        }
        public DataTable SelectSP(string key)
        {
            string query = "select * from SANPHAM where TenSanPham like '%'+@tensp+'%'";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@tensp", key);
            return conn.execSelectQuery(query, p);
        }

        public DataTable selectByID(long masp)
        {
            string query = "select * from SANPHAM where MaSanPham =@masp";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@masp", masp);
            return conn.execSelectQuery(query, p);
        }
        public bool insertSP(string tensp, int soluong, double gia, string loai, string mota)
        {
            if (soluong <= 0 || gia <= 0) return false;
            string query = "insert into SANPHAM values(@tensp,@soluong,@gia,@loai,@mota)";
            SqlParameter[] p = new SqlParameter[5];
            p[0] = new SqlParameter("@tensp", tensp);
            p[1] = new SqlParameter("@soluong", soluong);
            p[2] = new SqlParameter("@gia", gia);
            p[3] = new SqlParameter("@loai",loai);
            p[4] = new SqlParameter("@mota", mota);
            return conn.execInsertQuery(query, p);
        }
        public bool updateSP(long masp, string tensp, int soluong, double gia, string loai, string mota)
        {
            string query = "update SANPHAM set TenSanPham=@tensp,SoLuong=@soluong,Gia=@gia,LoaiSanPham=@loai,MoTa=@mota where MaSanPham=@masp";
            SqlParameter[] p = new SqlParameter[6];            
            p[0] = new SqlParameter("@tensp", tensp);
            p[1] = new SqlParameter("@soluong", soluong);
            p[2] = new SqlParameter("@gia", gia);
            p[3] = new SqlParameter("@loai", loai);
            p[4] = new SqlParameter("@mota", mota);
            p[5] = new SqlParameter("@masp",masp);
            return conn.execUpdateQuery(query,p);
        }
        public bool deleteSP(long masp)
        {
            string query = "delete from SANPHAM where MaSanPham = @masp";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@masp", masp);
            return conn.execDeleteQuery(query, p);
        }
        //public string getNameByID(int id)
        //{
        //    try
        //    {
        //        string query = "select * from SANPHAM where MaSanPham=@masp";
        //        SqlParameter[] p = new SqlParameter[1];
        //        p[0] = new SqlParameter("@masp", id);
        //        DataTable dt = new DataTable();
        //        dt = conn.execSelectQuery(query, p);
        //        if (dt != null)
        //        {
        //            return dt.Rows[0][1].ToString();
        //        }
        //        else return null;
        //    }
        //    catch(Exception exc)
        //    {
                
        //        return null;
        //    }
        //}
        //public int getPrice(int id)
        //{
        //    try
        //    {
        //        string query = "select * from SANPHAM where MaSanPham=@masp";
        //        SqlParameter[] p = new SqlParameter[1];
        //        p[0] = new SqlParameter("@masp", id);
        //        DataTable dt = new DataTable();
        //        dt = conn.execSelectQuery(query, p);
        //        if (dt != null)
        //        {
        //            return int.Parse(dt.Rows[0][3].ToString());
        //        }
        //        else return 0;
        //    }
        //    catch(Exception exc)
        //    {
        //        MessageBox.Show("Không có sản phẩm này");
        //        return 0;
        //    }

        //}
        //public DataTable NhapXuat(int thang, int nam)
        //{
        //    string query = "NhapXuat";
        //    SqlParameter[] p = new SqlParameter[2];
        //    p[0] = new SqlParameter("@thang", thang);
        //    p[1] = new SqlParameter("@nam", nam);
        //    return conn.execSelectProcedureQuery(query, p);
        //}
        
    }
}
